SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].[usp_StatisticsLectureUpdate]
	(
      @param_JOL_Statistics_StartTime VARCHAR(50),
      @param_JOL_Statistics_EndTime VARCHAR(50)
	)
AS
SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
DECLARE @Count int, @South int, @East int, @North int, @Zhuhai int, @TheOthers int

WHILE @param_JOL_Statistics_StartTime <= @param_JOL_Statistics_EndTime
BEGIN
	SET @South =(SELECT count(*)  
		FROM [dbo].[JOL_Lecture] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Lecture_Purpose = JOL_Field_No
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_ApplyPosition LIKE '%南校区%'
			AND JOL_Lecture_VerifyStatus = '已审核' )
	SET @East = (SELECT count(*)
		FROM [dbo].[JOL_Lecture] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Lecture_Purpose = JOL_Field_No
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_ApplyPosition LIKE '%东校区%'
			AND JOL_Lecture_VerifyStatus = '已审核')
	SET @North = (SELECT count(*) 
		FROM [dbo].[JOL_Lecture] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Lecture_Purpose = JOL_Field_No
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_ApplyPosition LIKE '%北校区%'
			AND JOL_Lecture_VerifyStatus = '已审核')
	SET @Zhuhai = (SELECT count(*) 
		FROM [dbo].[JOL_Lecture] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Lecture_Purpose = JOL_Field_No
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_ApplyPosition LIKE '%珠海校区%'
			AND JOL_Lecture_VerifyStatus = '已审核')
	SET @TheOthers = (SELECT count(*) 
		FROM [dbo].[JOL_Lecture] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Lecture_Purpose = JOL_Field_No
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_ApplyPosition NOT LIKE '%南校区%'
			AND JOL_Field_ApplyPosition NOT LIKE '%东校区%'
			AND JOL_Field_ApplyPosition NOT LIKE '%北校区%'
			AND JOL_Field_ApplyPosition NOT LIKE '%珠海校区%'
			AND JOL_Lecture_VerifyStatus = '已审核')

	SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_Lecture WHERE @param_JOL_Statistics_StartTime = Statistics_Date)
	IF (@Count = 0)
		BEGIN
			INSERT INTO [dbo].[JOL_Statistics_Lecture]
			(
				Statistics_Date,
				Statistics_South,
				Statistics_East,
				Statistics_North,
				Statistics_Zhuhai,
				Statistics_TheOthers,
				Statistics_AddTime,
				Statistics_UpdateTime
			)
			VALUES
			(
				@param_JOL_Statistics_StartTime,
				@South,
				@East,
				@North,
				@Zhuhai,
				@TheOthers,
				GETDATE(),
				GETDATE()
			)
		END
	ELSE
		BEGIN
			UPDATE [dbo].[JOL_Statistics_Lecture]
				SET Statistics_South = @South,
					Statistics_East = @East,
					Statistics_North = @North,
					Statistics_Zhuhai = @Zhuhai,
					Statistics_TheOthers = @TheOthers,
					Statistics_UpdateTime = GETDATE()
			WHERE Statistics_Date = @param_JOL_Statistics_StartTime
			
		END
	SET @param_JOL_Statistics_StartTime = CONVERT(VARCHAR(50), DATEADD(MONTH, 1, @param_JOL_Statistics_StartTime), 23)
END

GO
